package com.ssy.lingxi.report.dao.contract;

import com.ssy.lingxi.report.entity.report.ContractDayReportDO;
import com.ssy.lingxi.report.model.bo.tally.statistics.ContractCoordinationStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.ContractManageStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 *  合同管理DAO
 * @author ds
 * @date 2021/5/11
 * @version 2.0.0
 */
@Service
public class ContractDAO {

    @Resource
    @Qualifier("contractJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询合同管理统计数量
     * @return
     */
    public List<ContractManageStatisticsTallyVO> getStatusStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" partyamember_id memberId,partyarole_id roleId,")
                .append(" sum(case when outer_status in(1,3,5) then 1 else 0 end) toBeCommitCount,")
                .append(" sum(case when (outer_status = 4 and partyainner_status=3 )then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (outer_status = 4 and partyainner_status=5 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (outer_status = 4 and partyainner_status=7 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (outer_status = 4 and partyainner_status in (4,6,8,9) )then 1 else 0 end) toBeSignCount")
                .append(" FROM con_contract ")
                .append(" group by partyamember_id,partyarole_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ContractManageStatisticsTallyVO.class));
    }

    /**
     * 查询合同协同统计数量
     * @return
     */
    public List<ContractCoordinationStatisticsTallyVO> getStatusStatisticsCountByPartyB() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" partybmember_id memberId,partybrole_id roleId,")
                .append(" sum(case when (outer_status = 2 and partybinner_status=1 )then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (outer_status = 2 and partybinner_status=2 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (outer_status = 2 and partybinner_status=4 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (outer_status = 2 and partybinner_status in (3,5,6,7) )then 1 else 0 end) toBeSignCount")
                .append(" FROM con_contract ")
                .append(" group by partybmember_id,partybrole_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ContractCoordinationStatisticsTallyVO.class));
    }


    /**
     * 统计每日新增合同采购数据-[平台首页：采购统计]
     * @author ds
     * @date 2021/6/10
     * @param startTime: 开始时间
     * @param endTime: 结束时间
     * @return 统计结果
     **/
    public List<ContractDayReportDO> getNewlyAddedContractByDay(Long startTime, Long endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select to_char(TO_TIMESTAMP(operate_time/1000),'YYYY-MM-dd') dateTime,sum(total_amount) amount,sum(1) count")
                .append(" from con_contract_outer_record cr inner join con_contract c on cr.contract_id=c.id ")
                .append(" where cr.status=6 and operate_time between '" + startTime + "' and '" + endTime + "'")
                .append(" group by to_char(TO_TIMESTAMP(operate_time/1000),'YYYY-MM-dd');");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ContractDayReportDO.class));
    }
}
